﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OneMile.Entity.Model;
using System.Data.SqlClient;
using System.Data;
using OneMile.Common;
using OneMile.Entity;
using DALBase;
using System.Collections;

namespace OneMile.DAL
{
   public class DALUCommodity:BaseDAL
    {
       public DBAccess db=DBConnection.CreateDB();

       public IList<ECommodity> GetCommodityListByShopId(string ShopId,int PageNo,int PageSize,int SortType,int SortField)
       {
           String sql = @"
   SELECT (SELECT COUNT(1) FROM Fct_Coupon x WHERE x.Disabled=0 AND (GETDATE() BETWEEN x.StartDate AND DATEADD(DAY,1,x.EndDate)) AND x.CommodityId=F.CommodityId) AS CouponNum,
          Price,CommodityName,CommodityId,IsHot,IsNew,SmallCommodityImg,RowNum
     FROM ( 
           SELECT Price,CommodityName,CommodityId,IsHot,IsNew,SmallCommodityImg,ROW_NUMBER() OVER(ORDER BY {0} {1}) as RowNum
             FROM ( 
                  SELECT ROW_NUMBER() OVER(PARTITION BY a.CommodityId ORDER BY Price ASC) AS PriceNum,Price,CommodityName,a.CommodityId,IsHot,IsNew,SmallCommodityImg
                    FROM Fct_Commodity a
              INNER JOIN Rel_CommodityPrice b on a.CommodityId=b.CommodityId
                     AND ShopsId=@ShopsId
                  ) AS D 
              WHERE PriceNum=1
          ) AS F
    WHERE RowNum BETWEEN (@PageNo-1)*@PageSize+1 AND @PageNo*@PageSize";

           switch (SortField)
           {
               case (int)Enums.EnumCommoditySortField.CommodityName:
                   sql = String.Format(sql, Enums.EnumCommoditySortField.CommodityName.ToString(), SortType == (int)Enums.EnumSortType.ASC ? "ASC" : "DESC");
                   break;
               case (int)Enums.EnumCommoditySortField.IsHot:
                   sql = String.Format(sql, Enums.EnumCommoditySortField.IsHot.ToString(), SortType == (int)Enums.EnumSortType.ASC ? "ASC" : "DESC");
                   break;
               case (int)Enums.EnumCommoditySortField.IsNew:
                   sql = String.Format(sql, Enums.EnumCommoditySortField.IsNew.ToString(), SortType == (int)Enums.EnumSortType.ASC ? "ASC" : "DESC");
                   break;
               case (int)Enums.EnumCommoditySortField.Price:
                   sql = String.Format(sql, Enums.EnumCommoditySortField.Price.ToString(), SortType == (int)Enums.EnumSortType.ASC ? "ASC" : "DESC");
                   break;
               case (int)Enums.EnumCommoditySortField.Rec_CreateTime:
                   sql = String.Format(sql, Enums.EnumCommoditySortField.Rec_CreateTime.ToString(), SortType == (int)Enums.EnumSortType.ASC ? "ASC" : "DESC");
                   break;
               default:
                   sql = String.Format(sql, Enums.EnumCommoditySortField.CommodityName.ToString(), "DESC");
                   break;
           }
           IList<ECommodity> list = null;
           try
           {
               using (SqlCommand cmd = new SqlCommand(sql, Con))
               {
                   cmd.Parameters.Add(new SqlParameter("@ShopsId", ShopId));
                   cmd.Parameters.Add(new SqlParameter("@PageNo", PageNo));
                   cmd.Parameters.Add(new SqlParameter("@PageSize", PageSize));
                   DbOpen();
                   ECommodity commodity = null;
                   using (IDataReader dr = cmd.ExecuteReader())
                   {
                       list = new List<ECommodity>();
                       while (dr.Read())
                       {
                           commodity = new ECommodity();
                           Guid CommodityId = new Guid(dr["CommodityId"].TString());
                           commodity.CouponNum = dr["CouponNum"].TInt32();
                           commodity.CommodityId = CommodityId;
                           commodity.CommodityName = dr["CommodityName"].TString();
                           commodity.Price = dr["Price"].TDecimal();
                           commodity.IsHot = dr["IsHot"].TInt32() == 1 ? true : false;
                           commodity.IsNew = dr["IsNew"].TInt32() == 1 ? true : false;
                           list.Add(commodity);
                       }
                   }
                   return list;
               }
           }
           catch (Exception ex)
           {
               base.WriteLog("DALUCommodity_GetCommodityListByShopId-" + ex);
               return null;
           }
           finally
           {
               DbClose();
           }
       }

       /// <summary>
       /// 根据id获取该商品中价格最低的商品
       /// </summary>
       /// <param name="CommodityId"></param>
       /// <returns></returns>
       public ECommodity GetCommodityWithLowPriceByID(string CommodityId)
       {
           String sql = @"
SELECT Price,CommodityName,CommodityId,IsHot,IsNew,CommodityRemarks,
       (SELECT top 1 PromotionsTitle FROM Fct_Promotions X 
         WHERE X.CommodityId=D.CommodityId AND X.Disabled=0
               AND (GETDATE() BETWEEN X.StartDate AND DATEADD(DAY,1,X.EndDate)) 
      ORDER BY X.Rec_CreateTime desc) AS PromotionName
  FROM ( 
        SELECT ROW_NUMBER() OVER(PARTITION BY a.CommodityId ORDER BY Price ASC) AS PriceNum,Price,CommodityName,a.CommodityId,IsHot,IsNew,CommodityRemarks
          FROM Fct_Commodity a
    INNER JOIN Rel_CommodityPrice b on a.CommodityId=b.CommodityId
           AND a.CommodityId=@CommodityId
       ) AS D 
 WHERE PriceNum=1";

           try
           {
               ECommodity commodity = null;
               using (SqlCommand cmd = new SqlCommand(sql, Con))
               {
                   cmd.Parameters.Add(new SqlParameter("@CommodityId", CommodityId));
                   DbOpen();
                   using (IDataReader dr = cmd.ExecuteReader())
                   {
                       while (dr.Read())
                       {
                           commodity = new ECommodity();
                           Guid commodityid = new Guid(dr["CommodityId"].TString());
                           commodity.CommodityId = commodityid;
                           commodity.CommodityName = dr["CommodityName"].TString();
                           commodity.PromotionName = dr["PromotionName"].TString();
                           commodity.Price = dr["Price"].TDecimal();
                           commodity.IsHot = dr["IsHot"].TInt32() == 1 ? true : false;
                           commodity.IsNew = dr["IsNew"].TInt32() == 1 ? true : false;
                           commodity.CommodityRemarks = dr["CommodityRemarks"].TString();
                       }
                   }
                   return commodity; ;
               }
           }
           catch (Exception ex)
           {
               base.WriteLog("DALUCommodity_GetCommodityByID-" + ex);
               return null;
           }
           finally
           {
               DbClose();
           }
       }

       /// <summary>
       /// 获取属性列表（属性含有具体属性列表）
       /// </summary>
       /// <param name="commodityId"></param>
       /// <returns></returns>
       public IList<EAttribute> GetAttributeListById(string commodityId)
       {
           try
           {
               StringBuilder sb = new StringBuilder();
               sb.AppendLine("    SELECT A.CommodityId,A.CommodityName,A.CommodityRemarks,");
               sb.AppendLine("           B.AttributeId,C.AttributeName,C.[Description],");
               sb.AppendLine("           D.AttributeDetailsId,D.AttributeDetailsName");
               sb.AppendLine("      FROM Fct_Commodity A");
               sb.AppendLine("INNER JOIN Rel_CommodityAttribute B ON A.CommodityId= B.CommodityId");
               sb.AppendLine("INNER JOIN Dim_Attribute C ON B.AttributeId=C.AttributeId"); 
               sb.AppendLine("INNER JOIN Rel_AttributeDetails D ON C.AttributeId=D.AttributeId");
               sb.AppendLine("     WHERE A.CommodityId=@CommodityId");
               Hashtable hs=new Hashtable();
               hs.Add("CommodityId",commodityId);
               DataTable dt=db.ExecuteQuery(sb.ToString(),hs);
               if(dt!=null&&dt.Rows.Count>0)
               {
                   IList<EAttribute> list = new List<EAttribute>();
                   EAttribute ea = null;
                   EAttributeDetails ead=null;
                   //给搜索结果按照AttributeId分组
                   var result = dt.Rows.Cast<DataRow>().GroupBy<DataRow, string>(dr => dr["AttributeId"].TString());
                   foreach (var rows in result)
                   {
                       ea = new EAttribute();
                       IList<EAttributeDetails> listDetails = new List<EAttributeDetails>();
                       foreach (var dr in rows)
                       {
                         if(string.IsNullOrEmpty(ea.AttributeName))
                         {
                             ea.AttributeName = dr["AttributeName"].TString();
                             ea.AttributeId = new Guid(dr["AttributeId"].TString());
                             ea.Description = dr["Description"].TString();
                         }
                         ead = new EAttributeDetails();
                         ead.AttributeDetailsId = new Guid(dr["AttributeDetailsId"].TString());
                         ead.AttributeDetailsName = dr["AttributeDetailsName"].TString();
                         listDetails.Add(ead);
                       }
                       ea.AttributeDetailsList = listDetails;
                       list.Add(ea);
                   }
                   return list;
               }
               return null;
           }
           catch (Exception e)
           {
               
               base.WriteLog(e.Message);
               throw e;
           }
       }

       /// <summary>
       /// 获取规格列表
       /// </summary>
       /// <param name="commodityId"></param>
       /// <returns></returns>
       public IList<ESpecification> GetSpecificationListById(string commodityId)
       {
           try
           {
               StringBuilder sb = new StringBuilder();
               sb.AppendLine("    SELECT B.CommodityPriceId,B.SpecificationId,B.Price,C.SpecificationName,");
               sb.AppendLine("           C.[Description],C.Weight,C.Wide,C.Long,C.High");
               sb.AppendLine("      FROM Fct_Commodity A");
               sb.AppendLine("INNER JOIN Rel_CommodityPrice B ON A.CommodityId=B.CommodityId");
               sb.AppendLine("INNER JOIN Dim_Specification C ON B.SpecificationId=C.SpecificationId");
               sb.AppendLine("     WHERE A.CommodityId=@CommodityId");
               Hashtable hs=new Hashtable();
               hs.Add("CommodityId",commodityId);
               DataTable dt=db.ExecuteQuery(sb.ToString(),hs);
               if(dt!=null&&dt.Rows.Count>0)
               {
                   IList<ESpecification> list = new List<ESpecification>();
                   ESpecification es = null;
                   foreach(DataRow dr in dt.Rows )
                   {
                       es = new ESpecification();
                       es.SpecificatioId = new Guid(dr["SpecificationId"].TString());
                       es.CommodityPriceId = new Guid(dr["CommodityPriceId"].TString());
                       es.SpecificationName = dr["SpecificationName"].TString();
                       es.Description = dr["Description"].TInt32();
                       es.Price = dr["Price"].TDecimal();
                       es.Weight = dr["Weight"].TDecimal();
                       es.Wide = dr["Wide"].TDecimal();
                       es.Long = dr["Long"].TDecimal();
                       es.High = dr["High"].TDecimal();
                       list.Add(es);
                   }
                   return list;
               }
               return null;
           }
           catch (Exception e)
           {
               
               base.WriteLog(e.Message);
               throw e;
           }
       }

       /// <summary>
       /// 获取附件列表
       /// </summary>
       /// <param name="commodityId"></param>
       /// <returns></returns>
       public IList<ECommodityAccessories> GetCommodityAccessoriesListById(string commodityId)
       {
           try
           {
               StringBuilder sb = new StringBuilder();
               sb.AppendLine("    SELECT B.CommodityAccessoriesId,B.CommodityAccessoriesName,B.CommodityAccessoriesPrice");
               sb.AppendLine("      FROM Fct_Commodity A");
               sb.AppendLine("INNER JOIN Rel_CommodityAccessories B ON A.CommodityId=B.CommodityId");
               sb.AppendLine("     WHERE A.CommodityId=@CommodityId");
               Hashtable hs=new Hashtable();
               hs.Add("CommodityId",commodityId);
               DataTable dt=db.ExecuteQuery(sb.ToString(),hs);
               if(dt!=null&&dt.Rows.Count>0)
               {
                   IList<ECommodityAccessories> list=new List<ECommodityAccessories>();
                   ECommodityAccessories ca=null;
                   foreach(DataRow dr in dt.Rows )
                   {
                       ca=new ECommodityAccessories();
                       ca.CommodityAccessoriesId=new Guid(dr["CommodityAccessoriesId"].TString());
                       ca.CommodityAccessoriesName=dr["CommodityAccessoriesName"].TString();
                       ca.CommodityAccessoriesPrice=dr["CommodityAccessoriesPrice"].TDecimal();
                       list.Add(ca);
                   }
                   return list;
               }
               return null;
           }
           catch (Exception e)
           {
               
               base.WriteLog(e.Message);
               throw e;
           }
       }

       public ECommodity GetCommodityById(string commodityId)
       {
           try
           {
               StringBuilder sb = new StringBuilder();
               sb.AppendLine("SELECT [CommodityId],[ShopsId],[CommodityName],[CommodityRemarks],[IsHot],[IsNew]");
               sb.AppendLine("  FROM [Fct_Commodity] WHERE [Disabled]=0 AND [CommodityId]=@CommodityId");
               Hashtable hs = new Hashtable();
               hs.Add("CommodityId", commodityId);
               DataTable dt = db.ExecuteQuery(sb.ToString(), hs);
               if (dt!= null && dt.Rows.Count > 0)
               {
                   ECommodity commodity = new ECommodity();
                   commodity.CommodityId = new Guid(dt.Rows[0]["CommodityId"].TString());
                   commodity.ShopsId=new Guid(dt.Rows[0]["ShopsId"].TString());
                   commodity.CommodityName=dt.Rows[0]["CommodityName"].TString();
                   commodity.CommodityRemarks = dt.Rows[0]["CommodityRemarks"].TString();
                   commodity.IsHot = dt.Rows[0]["IsHot"].TInt32() == 1 ? true : false;
                   commodity.IsNew = dt.Rows[0]["IsNew"].TInt32() == 1 ? true : false;
                   return commodity;
               }
               return null;
           }
           catch (Exception e)
           {
               
               base.WriteLog(e.Message);
               throw e;
           }
       }

       public byte[] GetSmallCommodityImgByCommodityId(string CommodityId)
       {
           String sql = @"SELECT SmallCommodityImg
                            FROM Fct_Commodity a
                           WHERE CommodityId=@CommodityId";
           try
           {
               List<SqlParameter> list=new List<SqlParameter>();
               list.Add(new SqlParameter("@CommodityId", CommodityId));
               return (byte[])DBHelperSQL.sqlExecuteScalar(sql,list);
           }
           catch (Exception ex)
           {
               base.WriteLog("DALUCommodity_GetSmallCommodityImgByCommodityId-" + ex);
               return null;
           }
       }

       public byte[] GetBigCommodityImgByCommodityId(string CommodityId)
       {
           String sql = @"SELECT CommodityImg
                            FROM Fct_Commodity a
                           WHERE CommodityId=@CommodityId";
           try
           {
               List<SqlParameter> list = new List<SqlParameter>();
               list.Add(new SqlParameter("@CommodityId", CommodityId));
               return (byte[])DBHelperSQL.sqlExecuteScalar(sql, list);
           }
           catch (Exception ex)
           {
               base.WriteLog("DALUCommodity_GetSmallCommodityImgByCommodityId-" + ex);
               return null;
           }
       }
        

    }
}
